package cz.muni.fi.pv168.confusinglibrary;

import java.util.List;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Manages database of authors.
 *
 * @author Riema
 */
public class AuthorManagerImplDB implements AuthorManager{
    private Connection conn;
    private static final Logger logger = Logger.getLogger(
            AuthorManagerImplDB.class.getName());

    /**
     * CREATE TABLE APP.AUTHOR
     * (ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
     *  NAME VARCHAR(100),
     *  SURNAME VARCHAR(100),
     *  BORN DATE,
     *  DIED DATE
     *  );
     */
    public AuthorManagerImplDB(){
        try{
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/ConfusingLibraryData", "Riema", "Pa$$w0rd");
        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when connecting to DB", ex);
            throw new RuntimeException("Error when connecting to DB", ex);
        }
    }

    /**
     * Extracting primary key from a ResultSet.
     *
     * @param keys ResultSet with 1 primary key created by database
     * @return id of record
     * @throws SQLException if something goes wrong
     * @throws IllegalArgumentException if parameter contains too much rows or collumns, or no rows or collumns
     */
    private static Long getId(ResultSet keys) throws SQLException{
        if (keys.getMetaData().getColumnCount() != 1){
            throw new IllegalArgumentException("Given ResultSet contains more columns");
        }
        if (keys.next()){
            Long result = keys.getLong(1);
            if (keys.next()){
                throw new IllegalArgumentException("Given ResultSet contains more rows");
            }
            return result;
        }
        else{
            throw new IllegalArgumentException("Given ResultSet contain no rows");
        }
    }

    /**
     * This adds Author to database of already existing Authors.
     * It's expected that author isn't allready in database - ie. id is  null.
     *
     * @param author Author to be added, cannot be null, author's id is supposed to be null
     * @return Author after adding, ie. with proper id
     * @throws NullPointerException if parametr is null
     * @throws IllegalArgumentException if author has id (!= null)
     */
    public Author addAuthor(Author author){
        if (author == null){
            throw new NullPointerException("author");
        }
        if (author.getId() != null){
            throw new IllegalArgumentException("Author is already in list.");
        }

        PreparedStatement st = null;
        try{
            st = conn.prepareStatement("INSERT INTO APP.AUTHOR (name,surname,born,died) VALUES (?,?,?,?)",
                    Statement.RETURN_GENERATED_KEYS);
            st.setString(1, author.getName());
            st.setString(2, author.getSurname());
            st.setDate(3, (author.getBorn() == null ? null : new Date(author.getBorn().getTime())));
            st.setDate(4, (author.getDied() == null ? null : new Date(author.getDied().getTime())));

            assert (1 == st.executeUpdate());

            Long id = getId(st.getGeneratedKeys());
            author.setId(id);
        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when inserting grave into DB", ex);
            throw new RuntimeException("Error when inserting into DB", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }
        return author;
    }

    /**
     * This removes Author from database.
     * And sets null as id of removed Author.
     *
     * @param author Author to be removed, cannot be null
     * @throws NullPointerException if paramter is null
     * @throws IllegalArgumentException if author isn't in database or has null id
     */
    public void removeAuthor(Author author){
        if (author == null){
            throw new NullPointerException("author");
        }
        if (author.getId() == null){
            throw new IllegalArgumentException("Author's id is null");
        }

        PreparedStatement st = null;
        try{
            st = conn.prepareStatement("DELETE FROM APP.AUTHOR WHERE id=?");
            st.setLong(1, author.getId());

            if (st.executeUpdate() == 0){
                throw new IllegalArgumentException("Author doesn't exist");
            }

            author.setId(null);
        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when deleting author from DB", ex);
            throw new RuntimeException("Error when deleting author from DB", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }
    }

    /**
     * This updates information about Author.
     * Author is supposed to in database,
     * attributes can be changed all except id
     *
     * @param author Author with fixed id, and with updated information, cannot be null
     * @return  Author after updating
     * @throws NullPointerException if parametr is null
     * @throws IllegalArgumentException if author with this id isn't in database or id is null
     */
    public void updateAuthor(Author author){
        if (author == null){
            throw new NullPointerException("author");
        }
        if (author.getId() == null){
            throw new IllegalArgumentException("Author's id is null");
        }

        PreparedStatement st = null;
        try{
            st = conn.prepareStatement("UPDATE APP.AUTHOR SET name = ?, surname = ?, born = ?, died = ? WHERE ID = ?");
            st.setString(1, author.getName());
            st.setString(2, author.getSurname());
            st.setDate(3, (author.getBorn() == null ? null : new Date(author.getBorn().getTime())));
            st.setDate(4, (author.getDied() == null ? null : new Date(author.getDied().getTime())));
            st.setLong(5, author.getId());

            if (st.executeUpdate() < 1){
                throw new IllegalArgumentException("Author doesn't exist");
            }

        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when updating author", ex);
            throw new RuntimeException("Error when updating author", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }
    }

    /**
     *
     * @return List of all Authors, if there is no Author returns empty List
     */
    public List<Author> getAllAuthors(){
        PreparedStatement st = null;
        try{
            st = conn.prepareStatement("SELECT * from APP.AUTHOR");

            ResultSet rs = st.executeQuery();

            List<Author> found = new ArrayList<Author>();

            while (rs.next()){
                Author result = new Author();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setSurname(rs.getString("surname"));
                result.setBorn(rs.getDate("born") == null ? null : new java.util.Date(rs.getDate("born").getTime()));
                result.setDied(rs.getDate("died") == null ? null : new java.util.Date(rs.getDate("died").getTime()));
                found.add(result);
            }
            return Collections.unmodifiableList(found);
        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when getting all authors from DB", ex);
            throw new RuntimeException("Error when getting all authors from DB", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }
    }

    /**
     *
     * @param id we're looking for author having such id
     * @return Author with this id or null if there isn't such author
     * @throws NullPointerException if parametr is null
     */
    public Author findAuthorById(Long id){
        if (id == null){
            throw new NullPointerException("id");
        }

        PreparedStatement st = null;
        try{
            st = conn.prepareStatement(
                    "SELECT id, name, surname, born, died FROM APP.AUTHOR WHERE ID = ?");
            st.setLong(1, id);

            ResultSet rs = st.executeQuery();

            if (rs.next()){
                Author result = new Author();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setSurname(rs.getString("surname"));
                result.setBorn(rs.getDate("born") == null ? null : new java.util.Date(rs.getDate("born").getTime()));
                result.setDied(rs.getDate("died") == null ? null : new java.util.Date(rs.getDate("died").getTime()));
                assert !rs.next();
                return result;
            }
            else{
                return null;
            }

        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when getting author from DB in findAuthorById", ex);
            throw new RuntimeException("Error when getting author from DB in findAuthorById", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }
    }

    /**
     * All Authors with given surname
     *
     * @param surname parametr for searching - cannot be null
     * @return List of all Authors with this surname (can be empty)
     * @throws NullPointerException if parametr is null
     */
    public List<Author> findAuthorsByName(String surname){
        return findAuthorsByName(surname, null);
    }

    /**
     * All Authors with given surname and name
     *
     * @param surname parametr for searching - cannot be null
     * @param name option parametr for searching
     * @return List of all Authors with this surname and name (can be empty)
     * @throws NullPointerException if first parametr (surname) is null
     */
    public List<Author> findAuthorsByName(String surname, String name){
        if (surname == null){
            throw new NullPointerException("surname");
        }

        PreparedStatement st = null;
        try{
            if (name != null){
                st = conn.prepareStatement(
                        "SELECT id, name, surname, born, died FROM APP.AUTHOR WHERE SURNAME = ? AND NAME = ?");
                st.setString(1, surname);
                st.setString(2, name);
            }
            else{
                st = conn.prepareStatement(
                        "SELECT id, name, surname, born, died FROM APP.AUTHOR WHERE SURNAME = ?");
                st.setString(1, surname);
            }

            ResultSet rs = st.executeQuery();

            List<Author> authors = new ArrayList<Author>();

            while (rs.next()){
                Author result = new Author();
                result.setId(rs.getLong("id"));
                result.setName(rs.getString("name"));
                result.setSurname(rs.getString("surname"));
                result.setBorn(rs.getDate("born") == null ? null : new java.util.Date(rs.getDate("born").getTime()));
                result.setDied(rs.getDate("died") == null ? null : new java.util.Date(rs.getDate("died").getTime()));
                authors.add(result);
            }

            return authors;

        }
        catch (SQLException ex){
            logger.log(Level.SEVERE, "Error when getting authors from DB in findAuthorsByName", ex);
            throw new RuntimeException("Error when getting authors from DB in findAuthorsByName", ex);
        }
        finally{
            if (st != null){
                try{
                    st.close();
                }
                catch (SQLException ex){
                    logger.log(Level.WARNING, "Error when closing connection", ex);
                }
            }
        }

    }
}
